from flask import jsonify

from model.aval import Sites
from database import exec_sql_f, session_maker
from blues.mon import bp


# http://127.0.0.1:5000/api/v1/aval/list
@bp.route('/api/v1/aval/list', methods=['GET'])
def api_aval_list():
    d = {"message": "ok", "data": []}
    s = Sites()
    sites = s.find_all()
    # print(sites)
    d["data"] = sites
    for i in range(len(d["data"])):
        url = d["data"][i]['url']
        sql = """SELECT
        (SELECT COUNT(*) FROM aval_history WHERE url='{url}' AND up=1 AND time>DATE_SUB(NOW(),INTERVAL 1 HOUR)) 
        /
        (SELECT COUNT(*) FROM aval_history WHERE url='{url}' AND time>DATE_SUB(NOW(),INTERVAL 1 HOUR)) """
        d["data"][i]['aval_1h'] = str(exec_sql_f(sql.format(url=url)))
        sql = "SELECT ROUND(AVG(duration),2) FROM aval_history WHERE url='{url}' AND up=1 AND time>DATE_SUB(NOW(),INTERVAL 1 HOUR)"
        d["data"][i]['lat_1h'] = str(exec_sql_f(sql.format(url=url)))
        sql = "SELECT ROUND((SELECT COUNT(*) FROM aval_history WHERE url='{url}' AND up=1 AND time>DATE_SUB(NOW(),INTERVAL 5 MINUTE)) / 5 , 2)"
        d["data"][i]['acc_5m'] = str(exec_sql_f(sql.format(url=url)))
    print(d)
    return jsonify(d)



# http://127.0.0.1:5000/api/v1/aval/list
@bp.route('/api/v1/aval/history/<id>', methods=['GET'])
def api_aval_history(id):
    d = {"message": "ok", "data": {}}
    s = Sites()
    site = s.find_by_id(id)
    d["data"] = site

    # 初始化up,durantion,time
    d["data"]["up"] = []
    d["data"]["duration"] = []
    d["data"]["time"] = []
    # 数据库查询up,durantion,time等数据，并组成列表形式
    with session_maker() as session:
        sql = "SELECT up, date_format(time, '%m/%d %H:%i') as time,duration from aval_history WHERE url='{url}' AND time>DATE_SUB(NOW(),INTERVAL 24 HOUR) ORDER BY time ASC".format(url=d["data"]["url"])
        res = session.execute(sql).fetchall()
        for i in res:
            d["data"]["up"].append(i[0])
            d["data"]["time"].append(i[1])
            d["data"]["duration"].append(i[2])
    # print(d)
    return jsonify(d)